﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Environment;

namespace Portal.CDSP
{
    public class FAskQuestionsBLL 
    {
        public FAskQuestionsCls CreateModel(string ActiveLanguage, string AskId)
        {
            string Query = "select * from tableFAQ where askid=@AskId";
            
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@AskId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskId"].Value = AskId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                FAskQuestionsCls FAQCls = new FAskQuestionsCls();
                FAQCls.AskID = (string)dtResult.Rows[0]["AskID"];
                try
                {
                    FAQCls.AskID = (string)dtResult.Rows[0]["askid"];
                }
                catch { }
                try
                {

                    FAQCls.belongSite = (string)dtResult.Rows[0]["belongsite"];
                }
                catch { }

                try
                {
                    FAQCls.AskContent = (string)dtResult.Rows[0]["askcontent"];
                }
                catch { }
                
                try
                {
                    FAQCls.AskBy = (string)dtResult.Rows[0]["Askby"];
                }
                catch { }
                
                try
                {
                    FAQCls.AskAnswer = (string)dtResult.Rows[0]["askanswer"];

                }
                catch { }
                
                try
                {
                    FAQCls.AskEmail = (string)dtResult.Rows[0]["Email"];
                
                }
                catch { }

                try
                {
                    FAQCls.AskTime = (DateTime)dtResult.Rows[0]["AskTime"];
                }
                catch { }
                try
                {
                    
                    FAQCls.AskStatus = (Boolean)dtResult.Rows[0]["AskStatus"];
                }
                catch { }

                try
                {

                    FAQCls.AnswerBy = (string)dtResult.Rows[0]["AnswerBy"];
                }
                catch { }

                return FAQCls;
            }
        }


        public FAskQuestionsCls CreateModelWithShortId(string ActiveLanguage, string ShortId)
        {
            string Query = "select * from tableFAQ where ShortId=@ShortId";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ShortId"].Value = ShortId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                FAskQuestionsCls FAQCls = new FAskQuestionsCls();
                FAQCls.AskID = (string)dtResult.Rows[0]["AskID"];
                try
                {
                    FAQCls.AskID = (string)dtResult.Rows[0]["askid"];
                }
                catch { }
                try
                {

                    FAQCls.belongSite = (string)dtResult.Rows[0]["belongsite"];
                }
                catch { }

                try
                {
                    FAQCls.AskContent = (string)dtResult.Rows[0]["askcontent"];
                }
                catch { }

                try
                {
                    FAQCls.AskBy = (string)dtResult.Rows[0]["Askby"];
                }
                catch { }

                try
                {
                    FAQCls.AskAnswer = (string)dtResult.Rows[0]["askanswer"];

                }
                catch { }

                try
                {
                    FAQCls.AskEmail = (string)dtResult.Rows[0]["Email"];

                }
                catch { }

                try
                {
                    FAQCls.AskTime = (DateTime)dtResult.Rows[0]["AskTime"];
                }
                catch { FAQCls.AskTime = DateTime.Now; }
                try
                {

                    FAQCls.AskStatus = (Boolean)dtResult.Rows[0]["AskStatus"];
                }
                catch { }

                try
                {

                    FAQCls.ShortId = (string)dtResult.Rows[0]["ShortId"];
                }
                catch { }
                try
                {
                    FAQCls.AskTotalClick = (int)dtResult.Rows[0]["AskTotalClick"];
                }
                catch { FAQCls.AskTotalClick = 0; }

                try
                {
                    FAQCls.AnswerBy = (string)dtResult.Rows[0]["AnswerBy"];
                }
                catch { FAQCls.AnswerBy = "Admin"; }

                sqlCommand.Parameters.Clear();
                Query = "update tablefaq set asktotalclick = asktotalclick+1 where shortId=@ShortId";
                sqlCommand.CommandText = Query;
                sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar);
                sqlCommand.Parameters["@ShortId"].Value = ShortId;
                sqlCommand.ExecuteNonQuery();
                return FAQCls;
            }
        }

        public FAskQuestionsCls CreateView()
        {
            string Query = "select top 6 * from tableFAQ ";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
           
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                FAskQuestionsCls FAQCls = new FAskQuestionsCls();
                FAQCls.AskID = (string)dtResult.Rows[0]["AskID"];
                try
                {
                    FAQCls.AskID = (string)dtResult.Rows[0]["askid"];
                }
                catch { }
                try
                {

                    FAQCls.belongSite = (string)dtResult.Rows[0]["belongsite"];
                }
                catch { }

                try
                {
                    FAQCls.AskContent = (string)dtResult.Rows[0]["askcontent"];
                }
                catch { }

                try
                {
                    FAQCls.AskBy = (string)dtResult.Rows[0]["Askby"];
                }
                catch { }

                try
                {
                    FAQCls.AskAnswer = (string)dtResult.Rows[0]["askanswer"];

                }
                catch { }

                try
                {
                    FAQCls.AskEmail = (string)dtResult.Rows[0]["Email"];
                }
                catch { }

                try
                {
                    FAQCls.AskTime = (DateTime)dtResult.Rows[0]["AskTime"];
                }
                catch { }
                try
                {

                    FAQCls.AskStatus = (Boolean)dtResult.Rows[0]["AskStatus"];
                }
                catch { }
                return FAQCls;
            }
        }




        public DataTable Reading()
        {
            string Query = "select * from TableFAQ where askstatus = 1 order by asktime desc";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;

        }


        public DataTable ReadingForAdmin(int Status)
        {
            string Query = "select * from TableFAQ where AskStatus = @Status order by asktime desc";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@Status", SqlDbType.Int);
            sqlCommand.Parameters["@Status"].Value = Status;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;

        }
        public DataTable ReadingForShow(int top)
        {
            string Query = "select top "+ top +" * from TableFAQ where askstatus = 1 order by AskTime desc";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;

        }

        public DataTable ReadingForService(string belongSite)
        {
            string Query = "select * from TableFAQ where belongSite='" + belongSite + "' order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string ServerSideInsertFAQ(
            SqlTransaction sqlTransaction, 
            string belongSite, 
            string ActiveLanguage, 
            string AskContent,
            string AskBy,
            string Email,
            DateTime AskTime,
            string AskAnswer,
            Boolean AskStatus
            
            )
        {   bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if(HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            string AnswerBy = string.Empty;            
           
            string Query =
           "     INSERT INTO [TableFAQ]  " +
                  " ([AskID] " +
                  " ,[AskContent] " +
                  " ,[AskBy] " +
                  " ,[AskEmail] " +
                  " ,[AskTime] " +
                  " ,[AskTotalClick] " +
                  " ,[AskAnswer] " +
                  //" ,[AskTimeAns] " +
                  " ,[AskStatus] " +
                  " ,[belongSite] " +
                  " ,[AnswerBy] " +
                 "  ,[ShortId]) " +
            "VALUES " +
                 "      ( " +
                 "      @AskID " +
                 "      ,@AskContent " +
                 "      ,@AskBy " +
                 "      ,@AskEmail " +
                 "      ,@AskTime " +
                 "      ,@AskTotalClick " +
                 "      ,@AskAnswer " +
                 //"      ,@AskTimeAns " +
                 "      ,@AskStatus " +
                 "      ,@belongSite " +
                 "      ,@AnswerBy " +
                 "      ,@ShortId " +
                 "      )";
               
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            string AskId = System.Guid.NewGuid().ToString();
            sqlCommand.Parameters.Add("@AskID", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskID"].Value = AskId;

            sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar,8);
            sqlCommand.Parameters["@ShortId"].Value = AskId.Substring(0,8); // AskId.Substring(0, 8);

            sqlCommand.Parameters.Add("@AskContent", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskContent"].Value = AskContent;

            AskTime = DateTime.Now;
            sqlCommand.Parameters.Add("@AskTime", SqlDbType.DateTime);
            sqlCommand.Parameters["@AskTime"].Value = AskTime;

            sqlCommand.Parameters.Add("@AskBy", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskBy"].Value = AskBy;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@AskEmail", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskEmail"].Value = Email;

            sqlCommand.Parameters.Add("@AskAnswer", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskAnswer"].Value = AskAnswer;

            sqlCommand.Parameters.Add("@AskStatus", SqlDbType.Bit);
            sqlCommand.Parameters["@AskStatus"].Value = AskStatus;
            
            int AskTotal = 0;
            sqlCommand.Parameters.Add("@AskTotalClick", SqlDbType.Int);
            sqlCommand.Parameters["@AskTotalClick"].Value = AskTotal;

            sqlCommand.Parameters.Add("@AnswerBy", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AnswerBy"].Value = AnswerBy;

            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
                return AskId;
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }           
        }

     
        /* Update Status & Answer */

        public void ServerSideUpdateFAQ(SqlTransaction sqlTransaction, string ActiveLanguage, string AskID,string AskAnswer,bool AskStatus)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableFAQ set AskAnswer = @AskAnswer,AskStatus = @AskStatus where AskID=@AskID";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AskID", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskID"].Value = AskID;
            sqlCommand.Parameters.Add("@AskAnswer", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskAnswer"].Value = AskAnswer;
            
            sqlCommand.Parameters.Add("@AskStatus", SqlDbType.Bit);
            sqlCommand.Parameters["@AskStatus"].Value = AskStatus;

            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }
        /* Delete FAQ */
        public void ServerSideDeleteFAQ(SqlTransaction sqlTransaction, string AskID)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            // Environment.Permission.DeletePermission(sqlTransaction, AskID);
            string Query = " Delete from TableFAQ where AskID=@AskID";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AskID", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskID"].Value = AskID;

            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public string RewriteGetByCategoryUrl(string GetByCategoryUrl)
        {
            string reVal = GetByCategoryUrl;
            try
            {
                if (GetByCategoryUrl.Contains("-nd-"))
                {
                    reVal = GetByCategoryUrl.Replace("-nd-", "-qa-");
                }
            }
            catch { }
            return reVal;
        }


    }
}
